// var fs = require('fs');
// var file = 'test.db';//这里写的就是数据库文件的路径
// var exists = fs.existsSync(file);
// var sqlite3 = require('sqlite3').verbose();
// var db = new sqlite3.Database(file, function () {
//     db.run("create table test(name varchar(15))", function () {
//         db.run("insert into test values('hello,world')", function () {
//             db.all("select * from test", function (err, res) {
//                 if (!err)
//                     console.log(JSON.stringify(res));
//                 else
//                     console.log(err);
//             });
//         })
//     });
// });

var sqlite3 = require('sqlite3').verbose();

var fs = require('fs');
var file = './test2.db';
if (!fs.existsSync(file)) {
    console.log("Creating db file!");
    fs.openSync(file, 'w');
};



var optionTableSql = `
CREATE TABLE option (
key         CHAR(50)    PRIMARY KEY     NOT NULL,
value       CHAR(50)                    NOT NULL
);`;
var userTableSql = `
CREATE TABLE user (
user_id     CHAR(36)    PRIMARY KEY     NOT NULL,
username    CHAR(16)                    NOT NULL,
password    CHAR(16)                    NOT NULL,
nickname    CHAR(8)                     NOT NULL,
disable     INT(2)                      NOT NULL,
update_time INT(14),
create_time INT(14)
);`;
var serviceTableSql = `
CREATE TABLE service (
service_id      CHAR(36)    PRIMARY KEY     NOT NULL,
title           CHAR(16)                    NOT NULL,
ip              CHAR(500)                   NOT NULL,
username        CHAR(16)                    NOT NULL,
password        CHAR(16)                    NOT NULL,
update_time     INT(14),
create_time     INT(14)
);`;
var projectTableSql = `
CREATE TABLE project (
project_id  CHAR(36)    PRIMARY KEY     NOT NULL,
owner_user  CHAR(36)                    NOT NULL,
title       CHAR(16)                    NOT NULL,
description CHAR(500),
update_time INT(14),
create_time INT(14)
);`;
var folderTableSql = `
CREATE TABLE folder (
folder_id             CHAR(36)      PRIMARY KEY     NOT NULL,
folder_project_id     CHAR(36)                      NOT NULL,
folder_parent_id      CHAR(36),
title                 CHAR(255)                     NOT NULL,
type                  CHAR(255)                     NOT NULL,
description           CHAR(1000),
folder_index_array    CHAR(10000),
child_index_array     CHAR(10000),
disable               INT(2)                        NOT NULL,
update_time           INT(14),
create_time           INT(14)
);`;

var projectUserTableSql = `
CREATE TABLE project_user (
project_user_id     CHAR(36)      PRIMARY KEY   NOT NULL,
project_id          CHAR(36)                    NOT NULL,
user_id             CHAR(36)                    NOT NULL,
update_time         INT(14),
create_time         INT(14)
);`;
var projectUrlTableSql = `
CREATE TABLE project_url (
url_id              CHAR(36)      PRIMARY KEY   NOT NULL,
project_id          CHAR(36)                    NOT NULL,
str                 CHAR(500)                   NOT NULL,
title               CHAR(255)                   NOT NULL,
disable             INT(2)                      NOT NULL,
update_time         INT(14),
create_time         INT(14)
);`;
var projectModelTableSql = `
CREATE TABLE project_model (
model_id            CHAR(36)      PRIMARY KEY   NOT NULL,
project_id          CHAR(36)                    NOT NULL,
key                 CHAR(500)                   NOT NULL,
name                CHAR(255)                   NOT NULL,
text                CHAR(10000)                 NOT NULL,
update_time         INT(14),
create_time         INT(14)
);`;

var projectApiTableSql = `
CREATE TABLE project_api (
api_id          CHAR(36)      PRIMARY KEY     NOT NULL,
project_id      CHAR(36)                      NOT NULL,
folder_id       CHAR(36)                      NOT NULL,
title           CHAR(225)                     NOT NULL,
type            CHAR(10)                      NOT NULL,
body_type       INT(2),
disable         INT(2),
status          INT(2),
description     CHAR(200),   
url             CHAR(255),
header          CHAR(3000),
body            CHAR(3000),
fromdata        CHAR(3000),
query           CHAR(3000),
rest            CHAR(3000),
before          CHAR(3000),
after           CHAR(3000),
update_time     INT(14),
create_time     INT(14)
);`;

var projectFunctionTableSql = `
CREATE TABLE project_function (
function_id      CHAR(36)      PRIMARY KEY     NOT NULL,
project_id       CHAR(36)                      NOT NULL,
name             CHAR(225)                     NOT NULL,
key              CHAR(225)                     NOT NULL,
str              CHAR(10000)                   NOT NULL,
testStr          CHAR(255),
update_time      INT(14),
create_time      INT(14)
);`;

var projectCacheTableSql = `
CREATE TABLE project_cache (
cache_id        CHAR(36)      PRIMARY KEY     NOT NULL,
project_id      CHAR(36)                      NOT NULL,
key             CHAR(225)                     NOT NULL,
name            CHAR(225)                     NOT NULL,
update_time     INT(14),
create_time     INT(14)
);`;

var localCacheTableSql = `
CREATE TABLE local_api_cache (
cache_id        CHAR(36)      PRIMARY KEY     NOT NULL,
project_id      CHAR(36)                      NOT NULL,
service_id      CHAR(36)                      NOT NULL,
key             CHAR(255)                     NOT NULL,
value           CHAR(255)                     NOT NULL,
update_time     INT(14),
create_time     INT(14)
);`;
var projectCustomKeyTableSql = `
CREATE TABLE project_custom_key (
custom_key_id   CHAR(36)      PRIMARY KEY     NOT NULL,
project_id      CHAR(36)                      NOT NULL,
key             CHAR(255),
update_time     INT(14),
create_time     INT(14)
)`;



var initSql = optionTableSql
    + userTableSql
    + serviceTableSql
    + projectTableSql
    + folderTableSql
    + projectUserTableSql
    + projectUrlTableSql
    + projectModelTableSql
    + projectApiTableSql
    + projectFunctionTableSql
    + projectCacheTableSql
    + localCacheTableSql
    + projectCustomKeyTableSql;


var db = new sqlite3.Database(file, function (err) {
    db.run(initSql, function (err) {
        console.log(err);
    });
});









